<?php
require_once(SITE_ROOT . 'class/db/DB.class.php');
require_once(SITE_ROOT . 'class/bean/Country.class.php');

class DBCountry {
	
	public function getCountries($orderby = null) {
		$db = new DB();
		$connection = $db->connect();
		
		if ($orderby == "name") {
			$select = "SELECT `country#`, `name`, `englishname`, `seasons`, `divisions`, `activeteams`, `activeusers`, `waitingusers`, `coolness`, `date` FROM `tcountry` ORDER BY `name`";
		}
		else if ($orderby == "countryID") {
			$select = "SELECT `country#`, `name`, `englishname`, `seasons`, `divisions`, `activeteams`, `activeusers`, `waitingusers`, `coolness`, `date` FROM `tcountry` ORDER BY `country#`";
		}
		else $select = "SELECT `country#`, `name`, `englishname`, `seasons`, `divisions`, `activeteams`, `activeusers`, `waitingusers`, `coolness`, `date` FROM `tcountry` ORDER BY `activeteams`, `seasons`";
		
		if ($stmt = $connection->prepare($select)) {
			$stmt->execute();
			
			$stmt->bindColumn(1, $countryID, PDO::PARAM_INT);
			$stmt->bindColumn(2, $name);
			$stmt->bindColumn(3, $englishname);
			$stmt->bindColumn(4, $seasons, PDO::PARAM_INT);
			$stmt->bindColumn(5, $divisions, PDO::PARAM_INT);
			$stmt->bindColumn(6, $activeteams, PDO::PARAM_INT);
			$stmt->bindColumn(7, $activeusers, PDO::PARAM_INT);
			$stmt->bindColumn(8, $waitingusers, PDO::PARAM_INT);
			$stmt->bindColumn(9, $coolness);
			$stmt->bindColumn(10, $date);
			
			$countries = new ArrayObject();
			
			while ($stmt->fetch()) {
				$country = new Country();
				$country->setCountryID($countryID);
				$country->setName($name);
				$country->setEnglishName($englishname);
				$country->setSeasons($seasons);
				$country->setDivisions($divisions);
				$country->setActiveTeams($activeteams);
				$country->setActiveUsers($activeusers);
				$country->setWaitingUsers($waitingusers);
				$country->setCoolness($coolness);
				$country->setDate(strtotime($date));
				$countries->append($country);
			}			
			return $countries;
		}
	}
	
	public function getCountryByLeagueID($leagueID) {
		$db = new DB();
		$connection = $db->connect();
		
		if ($stmt = $connection->prepare("SELECT c.`country#`, c.`name`, c.`englishname`, c.`seasons`, c.`divisions`, c.`activeteams`, c.`activeusers`, c.`waitingusers`, c.`coolness`, c.`date` FROM `tcountry` c, `tleague` l WHERE l.`league#` = ? AND l.`country#` = c.`country#`")) {
			$stmt->execute(array($leagueID));
			
			$stmt->bindColumn(1, $countryID, PDO::PARAM_INT);
			$stmt->bindColumn(2, $name);
			$stmt->bindColumn(3, $englishname);
			$stmt->bindColumn(4, $seasons, PDO::PARAM_INT);
			$stmt->bindColumn(5, $divisions, PDO::PARAM_INT);
			$stmt->bindColumn(6, $activeteams, PDO::PARAM_INT);
			$stmt->bindColumn(7, $activeusers, PDO::PARAM_INT);
			$stmt->bindColumn(8, $waitingusers, PDO::PARAM_INT);
			$stmt->bindColumn(9, $coolness);
			$stmt->bindColumn(10, $date);
			
			$country = new Country();
			
			while ($stmt->fetch()) {
				$country->setCountryID($countryID);
				$country->setName($name);
				$country->setEnglishName($englishname);
				$country->setSeasons($seasons);
				$country->setDivisions($divisions);
				$country->setActiveTeams($activeteams);
				$country->setActiveUsers($activeusers);
				$country->setWaitingUsers($waitingusers);
				$country->setCoolness($coolness);
				$country->setDate(strtotime($date));
			}
			return $country;
		}
	}
	
	public function saveCountries($countries) {
		$db = new DB();
		$connection = $db->connect();
		
		if ($stmt = $connection->prepare('INSERT INTO `tcountry` (`country#`, `name`, `englishname`, `seasons`, `divisions`, `activeteams`, `activeusers`, `waitingusers`, `date`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE `name`=VALUES(`name`), `englishname`=VALUES(`englishname`), `seasons`=VALUES(`seasons`), `divisions`=VALUES(`divisions`), `activeteams`=VALUES(`activeteams`), `activeusers`=VALUES(`activeusers`), `waitingusers`=VALUES(`waitingusers`), `date`=VALUES(`date`)')) {
			foreach ($countries as $country) {
				$countryID = $country->getCountryID();
				$name = $country->getName();
				$englishname = $country->getEnglishName();
				$seasons = $country->getSeasons();
				$divisions = $country->getDivisions();
				$activeteams = $country->getActiveTeams();
				$activeusers = $country->getActiveUsers();
				$waitingusers = $country->getWaitingUsers();
				$date = date('Y-m-d', $country->getDate());
				
				$stmt->execute(array($countryID, $name, $englishname, $seasons, $divisions, $activeteams, $activeusers, $waitingusers, $date));
			}
		}
	}
	
	public function updateCoolness($countries) {
		$activeusers = $this->getActiveUsers();
		
		$db = new DB();
		$connection = $db->connect();
		
		if ($stmt = $connection->prepare('UPDATE `tcountry` SET `coolness` = ? WHERE `country#` = ?')) {
			foreach ($countries as $country) {
				$coolness = round(($activeusers/$country->getActiveTeams()), 2);
				$countryID = $country->getCountryID();
				
				$stmt->execute(array($coolness, $countryID));
			}
		}
	}
	
	public function getActiveTeams() {
		$db = new DB();
		$connection = $db->connect();
		
		if ($stmt = $connection->prepare('SELECT SUM(`activeteams`) AS `activeteams` FROM `tcountry`')) {
			$stmt->execute();
			$stmt->bindColumn(1, $activeteams);
			
			while ($stmt->fetch()) {
				$teams = $activeteams;
			}
			return $teams;
		}
	}
	
	public function getActiveUsers() {
		$db = new DB();
		$connection = $db->connect();
	
		if ($stmt = $connection->prepare('SELECT SUM(`activeusers`) AS `activeusers` FROM `tcountry`')) {
			$stmt->execute();
			$stmt->bindColumn(1, $activeusers);
				
			while ($stmt->fetch()) {
				$users = $activeusers;
			}
			return $users;
		}
	}
	
	public function getDate() {
		$db = new DB();
		$connection = $db->connect();
		
		if ($stmt = $connection->prepare('SELECT MAX(`date`) AS `date` FROM `tcountry`')) {
			$stmt->execute();
			$stmt->bindColumn(1, $date);
			
			while ($stmt->fetch()) {
				$fetchdate = $date;
			}
			return strtotime($fetchdate);
		}
	}
	
}

?>